﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using XMK.Framework.DataAccess;
using XMK.Tadong.Model;
using XMK.Utils;
using XMK.Framework.Model;
using XMK.Tadong.Model.Cond;


namespace XMK.Tadong.Data
{
    public class Tb_TeamData
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        protected DbProvider dbProvider;

        public Tb_TeamData(DbProvider dbProvider)
        {
            this.dbProvider = dbProvider;

        }
        /// <summary>
        /// 根据课题组名获得课题组Id
        /// </summary>
        /// <param name="teamName"></param>
        /// <returns></returns>
        public string GetTeamIdbyName(string teamName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TEAM_ID FROM TB_TEAM WHERE TEAM_NAME = '" + teamName + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Rows[0][0].ToString();
            }
            return null;
        }
        /// <summary>
        /// 判断是否已有课题组
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public bool IsExist(TB_TEAMInfo teamObj)
        {
            string leader = teamObj.TEAM_LEADER;
            string name = teamObj.TEAM_NAME;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(*) FROM TB_TEAM WHERE TEAM_NAME = '" + name + "' and TEAM_LEADER='" + leader + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd);

            return (Convert.ToInt16(obj) != 0);


        }


        public DataTable QueryCstInfo(string type)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM tb_cst_info WHERE CST_TYPE = " + type);
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds;
            }
            return null;
        }
        //绑定密级
        public DataTable GetMJ(string Type)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM base_dictitems WHERE DICTTYPE_ID = " + Type);
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds;
            }
            return null;
        }



        /// <summary>
        /// 用户编辑 
        /// </summary>
        /// <param name="cond"></param>
        /// <returns></returns>
        public bool DeleteTeamInfo(string teamId)
        {
            StringBuilder strSql = new StringBuilder();

            List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, "delete tb_team WHERE TEAM_ID=:p_TEAM_ID",
                delegate()
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "TEAM_ID").ToArray();
                });
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmd.SetParamValues(teamId);
            cmdlist.Add(cmd.DbCmdInfo);
            return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());

        }




        /// <summary>
        /// 添加新课题组
        /// </summary>
        /// <returns></returns>
        public bool InsertNewTeam(TB_TEAMInfo teamObj)
        {
            bool result = true;
            string sql = @"insert into TB_TEAM(TEAM_ID, TEAM_NAME,TEAM_STS ,CREATE_USER ,TEAM_LEADER, TEAM_DESCE,CREATE_TIME,UPD_TIME,
 ROLES_ID,OUTHELP,PRO_TYPE,FAR_PRO,RES_MANAGE,PRO_LEVEL,PROFE_TYPE,HEL_CONTR,PRO_LEADER,
ADJUS_INFO,EXPERT_GROUP,DELAY_INFO,PRO_STS,PRO_FUND,PRO_EVA,START_DATE,END_DATE)
                                             values(:p_TEAM_ID,:p_TEAM_NAME,:p_TEAM_STS, :p_CREATE_USER, :p_TEAM_LEADER, :p_TEAM_DESCE, 
:p_CREATE_TIME,:p_UPD_TIME,:p_ROLES_ID,:p_OUTHELP,:p_PRO_TYPE,:p_FAR_PRO,
:p_RES_MANAGE,:p_PRO_LEVEL,:p_PROFE_TYPE,:p_HEL_CONTR,:p_PRO_LEADER,:p_ADJUS_INFO,:p_EXPERT_GROUP,
:p_DELAY_INFO,:p_PRO_STS,:p_PRO_FUND,:p_PRO_EVA,:p_START_DATE,:p_END_DATE)";

            //新建一个数据库操作帮助类
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql,
                //参数生成
                delegate()
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "TEAM_ID", "TEAM_NAME", "TEAM_STS", "CREATE_USER", "TEAM_LEADER", "TEAM_DESCE", "CREATE_TIME", "UPD_TIME", "ROLES_ID", "OUTHELP", "PRO_TYPE", "FAR_PRO", "RES_MANAGE", "PRO_LEVEL", "PROFE_TYPE", "HEL_CONTR", "PRO_LEADER", "ADJUS_INFO", "EXPERT_GROUP", "DELAY_INFO", "PRO_STS", "PRO_FUND", "PRO_EVA", "START_DATE", "END_DATE").ToArray();
                });
            //参数设值
            cmd.SetParamValues(teamObj.TEAM_ID, teamObj.TEAM_NAME, teamObj.TEAM_STS, teamObj.CREATE_USER, teamObj.TEAM_LEADER, teamObj.TEAM_DESCE, teamObj.CREATE_TIME, teamObj.UPD_TIME, teamObj.ROLES_ID, teamObj.OUTHELP, teamObj.PRO_TYPE, teamObj.FAR_PRO, teamObj.RES_MANAGE, teamObj.PRO_LEVEL, teamObj.PROFE_TYPE, teamObj.HEL_CONTR, teamObj.PRO_LEADER, teamObj.ADJUS_INFO, teamObj.EXPERT_GROUP, teamObj.DELAY_INFO, teamObj.PRO_STS, teamObj.PRO_FUND, teamObj.PRO_EVA, teamObj.START_DATE, teamObj.END_DATE);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            int back = DbManager.ExecNonQuery(cmd);
            if (back == 0)
                result = false;

            return result;

        }

        /// <summary>
        /// 添加新课题组
        /// </summary>
        /// <returns></returns>
        public bool UpdateTeamInfo(TB_TEAMInfo teamObj)
        {
            bool result = true;
            string sql = @"update  TB_TEAM  SET TEAM_NAME =:p_TEAM_NAME,TEAM_STS=:p_TEAM_STS ,TEAM_LEADER=:p_TEAM_LEADER,TEAM_DESCE=:p_TEAM_DESCE ,UPD_TIME=:p_UPD_TIME , ROLES_ID  =:p_ROLES_ID  , OUTHELP =:p_OUTHELP , PRO_TYPE =:p_PRO_TYPE , FAR_PRO =:p_FAR_PRO  , RES_MANAGE =:p_RES_MANAGE  , PRO_LEVEL =:p_PRO_LEVEL  , PROFE_TYPE =:p_PROFE_TYPE  , HEL_CONTR =:p_HEL_CONTR  , PRO_LEADER =:p_PRO_LEADER  , 
ADJUS_INFO =:p_ADJUS_INFO  , EXPERT_GROUP =:p_EXPERT_GROUP  , DELAY_INFO =:p_DELAY_INFO  , PRO_STS =:p_PRO_STS  , PRO_FUND =:p_PRO_FUND  , PRO_EVA =:p_PRO_EVA  , START_DATE =:p_START_DATE , END_DATE=:p_END_DATE  where TEAM_ID=:p_TEAM_ID";

            //新建一个数据库操作帮助类
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql,
                //参数生成
                delegate()
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "TEAM_ID", "TEAM_NAME", "TEAM_STS",  "TEAM_LEADER", "TEAM_DESCE",  "UPD_TIME", "ROLES_ID", "OUTHELP", "PRO_TYPE", "FAR_PRO", "RES_MANAGE", "PRO_LEVEL", "PROFE_TYPE", "HEL_CONTR", "PRO_LEADER", "ADJUS_INFO", "EXPERT_GROUP", "DELAY_INFO", "PRO_STS", "PRO_FUND", "PRO_EVA", "START_DATE", "END_DATE").ToArray();
                });
            //参数设值
            cmd.SetParamValues(teamObj.TEAM_ID, teamObj.TEAM_NAME, teamObj.TEAM_STS,  teamObj.TEAM_LEADER, teamObj.TEAM_DESCE,  teamObj.UPD_TIME, teamObj.ROLES_ID, teamObj.OUTHELP, teamObj.PRO_TYPE, teamObj.FAR_PRO, teamObj.RES_MANAGE, teamObj.PRO_LEVEL, teamObj.PROFE_TYPE, teamObj.HEL_CONTR, teamObj.PRO_LEADER, teamObj.ADJUS_INFO, teamObj.EXPERT_GROUP, teamObj.DELAY_INFO, teamObj.PRO_STS, teamObj.PRO_FUND, teamObj.PRO_EVA, teamObj.START_DATE, teamObj.END_DATE);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            int back = DbManager.ExecNonQuery(cmd);
            if (back == 0)
                result = false;

            return result;

        }



        /// <summary>
        /// 通过ID号获取对象
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public TB_TEAMInfo GetTeamInfoById(string teamId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM TB_TEAM WHERE TEAM_ID = " + teamId);

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            TB_TEAMInfo Obj = DbManager.GetInfo(cmd,
                delegate(out TB_TEAMInfo u, DataRow row)
                {
                    u = new TB_TEAMInfo
                    {
                        TEAM_ID = ParseHelper.Parse<decimal>(row["TEAM_ID"]),
                        TEAM_NAME = ParseHelper.Parse<string>(row["TEAM_NAME"]),
                        TEAM_STS = ParseHelper.Parse<decimal>(row["TEAM_STS"]),
                        CREATE_USER = ParseHelper.Parse<string>(row["CREATE_USER"]),
                        TEAM_LEADER = ParseHelper.Parse<string>(row["TEAM_LEADER"]),
                        TEAM_DESCE = ParseHelper.Parse<string>(row["TEAM_DESCE"]),
                        CREATE_TIME = ParseHelper.Parse<DateTime>(row["CREATE_TIME"]),
                        UPD_TIME = ParseHelper.Parse<DateTime>(row["UPD_TIME"]),

                        ROLES_ID = ParseHelper.Parse<string>(row["ROLES_ID"]),
                        OUTHELP = ParseHelper.Parse<string>(row["OUTHELP"]),//Convert.IsDBNull(row["PRO_TYPE"]) ? null :
                        // PRO_TYPE =  ParseHelper.Parse<decimal?>(row["PRO_TYPE"]),
                        PRO_TYPE = Convert.IsDBNull(row["PRO_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_TYPE"]),
                        FAR_PRO = Convert.IsDBNull(row["FAR_PRO"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["FAR_PRO"]),
                        RES_MANAGE = ParseHelper.Parse<string>(row["RES_MANAGE"]),
                        PRO_LEVEL = Convert.IsDBNull(row["PRO_LEVEL"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_LEVEL"]),
                        PROFE_TYPE = Convert.IsDBNull(row["PROFE_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PROFE_TYPE"]),
                        HEL_CONTR = ParseHelper.Parse<string>(row["HEL_CONTR"]),
                        PRO_LEADER = ParseHelper.Parse<string>(row["PRO_LEADER"]),
                        ADJUS_INFO = ParseHelper.Parse<string>(row["ADJUS_INFO"]),
                        EXPERT_GROUP = ParseHelper.Parse<string>(row["EXPERT_GROUP"]),
                        DELAY_INFO = ParseHelper.Parse<string>(row["DELAY_INFO"]),
                        PRO_STS = ParseHelper.Parse<string>(row["PRO_STS"]),
                        PRO_FUND = Convert.IsDBNull(row["PRO_FUND"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_FUND"]),
                        PRO_EVA = ParseHelper.Parse<string>(row["PRO_EVA"]),
                        START_DATE = ParseHelper.Parse<DateTime>(row["START_DATE"]),
                        END_DATE = ParseHelper.Parse<DateTime>(row["END_DATE"])


                    };
                });
            return Obj;

        }



        /// <summary>
        /// 获取课题组信息列表
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public List<TB_TEAMInfo> GetTeamInfoList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM TB_TEAM  ");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;

            List<TB_TEAMInfo> teamList = DbManager.GetInfoList<TB_TEAMInfo>(cmd,
                delegate(out TB_TEAMInfo u, DataRow row)
                {
                    u = new TB_TEAMInfo
                    {
                        TEAM_ID = ParseHelper.Parse<decimal>(row["TEAM_ID"]),
                        TEAM_NAME = ParseHelper.Parse<string>(row["TEAM_NAME"]),
                        TEAM_STS = ParseHelper.Parse<decimal>(row["TEAM_STS"]),
                        CREATE_USER = ParseHelper.Parse<string>(row["CREATE_USER"]),
                        TEAM_LEADER = ParseHelper.Parse<string>(row["TEAM_LEADER"]),
                        TEAM_DESCE = ParseHelper.Parse<string>(row["TEAM_DESCE"]),
                        CREATE_TIME = ParseHelper.Parse<DateTime>(row["CREATE_TIME"]),
                        UPD_TIME = ParseHelper.Parse<DateTime>(row["UPD_TIME"]),

                        ROLES_ID = ParseHelper.Parse<string>(row["ROLES_ID"]),
                        OUTHELP = ParseHelper.Parse<string>(row["OUTHELP"]),//Convert.IsDBNull(row["PRO_TYPE"]) ? null :
                        // PRO_TYPE =  ParseHelper.Parse<decimal?>(row["PRO_TYPE"]),
                        PRO_TYPE = Convert.IsDBNull(row["PRO_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_TYPE"]),
                        FAR_PRO = Convert.IsDBNull(row["FAR_PRO"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["FAR_PRO"]),
                        RES_MANAGE = ParseHelper.Parse<string>(row["RES_MANAGE"]),
                        PRO_LEVEL = Convert.IsDBNull(row["PRO_LEVEL"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_LEVEL"]),
                        PROFE_TYPE = Convert.IsDBNull(row["PROFE_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PROFE_TYPE"]),
                        HEL_CONTR = ParseHelper.Parse<string>(row["HEL_CONTR"]),
                        PRO_LEADER = ParseHelper.Parse<string>(row["PRO_LEADER"]),
                        ADJUS_INFO = ParseHelper.Parse<string>(row["ADJUS_INFO"]),
                        EXPERT_GROUP = ParseHelper.Parse<string>(row["EXPERT_GROUP"]),
                        DELAY_INFO = ParseHelper.Parse<string>(row["DELAY_INFO"]),
                        PRO_STS = ParseHelper.Parse<string>(row["PRO_STS"]),
                        PRO_FUND = Convert.IsDBNull(row["PRO_FUND"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_FUND"]),
                        PRO_EVA = ParseHelper.Parse<string>(row["PRO_EVA"]),
                        START_DATE = ParseHelper.Parse<DateTime>(row["START_DATE"]),
                        END_DATE = ParseHelper.Parse<DateTime>(row["END_DATE"])


                    };
                });
            return teamList;

        }
       
        /// <summary>
        /// 获取当前课题组长管理的课题组信息列表
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public List<TB_TEAMInfo> QueryMyManaTeamList(String team_leaderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM TB_TEAM where TEAM_LEADER='" + team_leaderID + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;

            List<TB_TEAMInfo> teamList = DbManager.GetInfoList<TB_TEAMInfo>(cmd,
                delegate(out TB_TEAMInfo u, DataRow row)
                {
                    u = new TB_TEAMInfo
                    {
                        TEAM_ID = ParseHelper.Parse<decimal>(row["TEAM_ID"]),
                        TEAM_NAME = ParseHelper.Parse<string>(row["TEAM_NAME"]),
                        TEAM_STS = ParseHelper.Parse<decimal>(row["TEAM_STS"]),
                        CREATE_USER = ParseHelper.Parse<string>(row["CREATE_USER"]),
                        TEAM_LEADER = ParseHelper.Parse<string>(row["TEAM_LEADER"]),
                        TEAM_DESCE = ParseHelper.Parse<string>(row["TEAM_DESCE"]),
                        CREATE_TIME = ParseHelper.Parse<DateTime>(row["CREATE_TIME"]),
                        UPD_TIME = ParseHelper.Parse<DateTime>(row["UPD_TIME"]),

                        ROLES_ID = ParseHelper.Parse<string>(row["ROLES_ID"]),
                        OUTHELP = ParseHelper.Parse<string>(row["OUTHELP"]),//Convert.IsDBNull(row["PRO_TYPE"]) ? null :
                        // PRO_TYPE =  ParseHelper.Parse<decimal?>(row["PRO_TYPE"]),
                        PRO_TYPE = Convert.IsDBNull(row["PRO_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_TYPE"]),
                        FAR_PRO = Convert.IsDBNull(row["FAR_PRO"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["FAR_PRO"]),
                        RES_MANAGE = ParseHelper.Parse<string>(row["RES_MANAGE"]),
                        PRO_LEVEL = Convert.IsDBNull(row["PRO_LEVEL"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_LEVEL"]),
                        PROFE_TYPE = Convert.IsDBNull(row["PROFE_TYPE"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PROFE_TYPE"]),
                        HEL_CONTR = ParseHelper.Parse<string>(row["HEL_CONTR"]),
                        PRO_LEADER = ParseHelper.Parse<string>(row["PRO_LEADER"]),
                        ADJUS_INFO = ParseHelper.Parse<string>(row["ADJUS_INFO"]),
                        EXPERT_GROUP = ParseHelper.Parse<string>(row["EXPERT_GROUP"]),
                        DELAY_INFO = ParseHelper.Parse<string>(row["DELAY_INFO"]),
                        PRO_STS = ParseHelper.Parse<string>(row["PRO_STS"]),
                        PRO_FUND = Convert.IsDBNull(row["PRO_FUND"]) ? null : (decimal?)ParseHelper.Parse<decimal>(row["PRO_FUND"]),
                        PRO_EVA = ParseHelper.Parse<string>(row["PRO_EVA"]),
                        START_DATE = ParseHelper.Parse<DateTime>(row["START_DATE"]),
                        END_DATE = ParseHelper.Parse<DateTime>(row["END_DATE"])


                    };
                });
            return teamList;

        }


        /// <summary>
        /// 通过userid获取当前用户所在的课题组
        /// </summary>
        /// <returns>文件列表</returns>
        public DataTable GetTeamListBySql(string User_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT a.*,b.team_name FROM TB_TEAM_USER a left join TB_TEAM b on a.team_id = b.team_id where a.user_id='" + User_ID + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Copy();
            }
            return null;

        }

        public decimal getMaxTeamId()
        {
        
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT max(team_id) from tb_team ");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            object obj = DbManager.ExecScalar(cmd);

            return Convert.ToDecimal(obj);
            //return (Convert.ToInt16(obj) != 0);

        }

        public bool isXmkAdmin( string userId)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select team_id  from tb_team where team_id=17 and  team_leader='" + userId + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null&&ds.Rows.Count>0)
            {
                return true;
            }
            return false;

        }

    }
}
